﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using Settings;
using Utils;
using System.Data;

namespace PanasonicWarranty.Business
{
    public partial class pa_WarrantyStatusStruct
    {
        public int WarrantyStatusID { get; set; }
        public int WarrantyID { get; set; }
        public string WarrantyStatusName { get; set; }
        public bool Check { get; set; }

        public static List<pa_WarrantyStatusStruct> GetByWarranty(int warrantyID)
        {
            using (SqlConnection conn = new SqlConnection(AppSetting.ConnectionString))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = @"
                    SELECT WarrantyStatusID, @WarrantyID WarrantyID, WarrantyStatusName, CAST(1 AS BIT) [Check] 
                    FROM pa_WarrantyStatus pws
                    WHERE pws.WarrantyStatusID IN (
	                    SELECT WarrantyStatusID FROM pa_Warranty_WarrantyStatus p WHERE p.WarrantyID = @WarrantyID	
                    )
                    UNION
                    SELECT WarrantyStatusID, @WarrantyID WarrantyID, WarrantyStatusName, CAST(0 AS BIT) [Check] 
                    FROM pa_WarrantyStatus pws
                    WHERE pws.WarrantyStatusID NOT IN (
	                    SELECT WarrantyStatusID FROM pa_Warranty_WarrantyStatus p WHERE p.WarrantyID = @WarrantyID	
                    )";
                    cmd.Parameters.AddWithValue("@WarrantyID", warrantyID);
                    return ObjectClass.CreateList<pa_WarrantyStatusStruct>(cmd.ExecuteReader());
                }
            }
        }


        public static void Insert(string warrantyID, List<int> warrantyStatusID)
        {
            DataTable dtSource = new DataTable();
            dtSource.Columns.Add("WarrantyID", typeof(int));
            dtSource.Columns.Add("WarrantyStatusID", typeof(int));
            foreach (var item in warrantyStatusID)
            {
                dtSource.Rows.Add(warrantyID, item);
            }
            using (SqlConnection conn = new SqlConnection(AppSetting.ConnectionString))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = string.Format("DELETE FROM pa_Warranty_WarrantyStatus WHERE WarrantyID = {0}", warrantyID);
                    cmd.ExecuteNonQuery();
                }
                using (SqlBulkCopy bc = new SqlBulkCopy(conn))
                {
                    bc.DestinationTableName = "pa_Warranty_WarrantyStatus";
                    bc.BulkCopyTimeout = 30;
                    bc.WriteToServer(dtSource);
                }
            }
            
        }
    }
}
